/**
 * Company : TIS.
 * Created By : Chatree Siangklom
 * Created On : Aug 21, 2013  5:41:35 PM 
 * e-Mail : dr_chatree@hotmail.com
 * Mobile Phone : (+66) 087-921-5115
 * Package Name : com.tis.database.dao.impl
 * File Name : AbstractDaoImpl.java
 * TODO
 */
package com.tis.database.dao.impl;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.criterion.Criterion;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import promis.utility.CommonService;
import promis.utility.Utilitys;
import sap.business.enumeration.STAGING_STATUS;
import com.tis.database.dao.AbstractDao;
import com.tis.database.dao.ClsInstance;
import com.tis.database.entitys.staging.FiCreditNoteOutb;
import com.tis.database.entitys.staging.FiDepositReturnOutb;
import com.tis.database.entitys.staging.FiInvReceiptOutb;
import com.tis.database.entitys.staging.FiInvoiceOutb;
import com.tis.database.entitys.staging.SdCustomerOutb1;
import com.tis.database.entitys.staging.StampObject;
/**
 * @author Administrator
 *
 */
public abstract class AbstractDaoImpl<E, I extends Serializable>  implements AbstractDao<E,I> {
	
	private static final Logger logging = LoggerFactory.getLogger(AbstractDaoImpl.class);

    private Class<E> entityClass;
    
    protected AbstractDaoImpl(Class<E> entityClass) {
        this.entityClass = entityClass;
    }
    
    @Autowired
    private SessionFactory sessionFactory;

	public Session getCurrentSession() throws HibernateException {
        return sessionFactory.getCurrentSession();        
    }
        
    @Override   
    public E findById(I id) throws HibernateException {
    	try {
    		Session session = getCurrentSession(); 
    		
    		try{
    			
    			E eobj = (E) session.get(entityClass, id);
    			
    			return eobj;
    			
			}catch(HibernateException ee){
				
    			ee.printStackTrace();
    			throw ee;
    			
    		}finally{
    			session.flush();
    		}
    		
    	}catch(HibernateException ex)
    	{
    		ex.printStackTrace();
    		throw ex;
    	}
    }
    
    @Override
    public void save(E e) throws HibernateException {
    	try {
    		Session session = getCurrentSession();
    		Transaction tx = session.beginTransaction();
    		
    		try{
    			
    			session.save(e);
    			
    			tx.commit();
    			
    		}catch(HibernateException ee){
    			
    			if (tx!=null)  tx.rollback();
    			ee.printStackTrace();
    			throw ee;
    			
    		}finally{
    			session.flush();
    		}
    		    		
    	}catch(HibernateException ex)
    	{
    		ex.printStackTrace();
    		throw ex;
    	}
    }

    @Override
    public void save(List<E> e) throws HibernateException {    	
    	Session session = getCurrentSession();
    	Transaction tx = null;
    	try {
    		tx = session.beginTransaction();    		
    		
    		for(E ee : e) {
    			session.save(ee);
    		}
    		
    		tx.commit();
    		session.flush();
    	}catch(HibernateException ex) {
    		if (tx!=null) tx.rollback();
            ex.printStackTrace();
            throw ex;
    	}
    }
    
    @Override
    public void saveOrUpdate(E e) throws HibernateException {
    	try {
    		
    		Session session = getCurrentSession();    
    		Transaction tx = session.beginTransaction();
    		
    		try{
    			
    			session.saveOrUpdate(e);     
    			
    			tx.commit();
            	session.flush();
            	
			}catch(HibernateException ee){
    			
    			if (tx!=null)  tx.rollback();
    			ee.printStackTrace();
    			throw ee;
    			
    		}finally{
    			session.flush();
    		}
    		
    	}catch(HibernateException ex) {
    		ex.printStackTrace();
    		throw ex;
    	}
    }

    @Override
    public void saveOrUpdate(List<E> e) throws HibernateException {
    	Session session = getCurrentSession();
    	Transaction tx = null;
    	try {
    		tx = session.beginTransaction();    		
    		
    		for(E ee : e) {
    			session.saveOrUpdate(ee);
    		}
    		
    		tx.commit();
    		session.flush();
    	}catch(HibernateException ex) {
    		if (tx!=null) tx.rollback();
            ex.printStackTrace();
            throw ex;
    	}finally{
    		session.flush();
    	}
    }
    
    @Override
    public void delete(E e) throws HibernateException {
    	try {
    		
    		Session session = getCurrentSession();    	
    		Transaction tx = session.beginTransaction();
        	try{
        		
        		session.delete(e);
        		tx.commit();
        		
        	}catch(HibernateException ee){
    			
    			if (tx!=null)  tx.rollback();
    			ee.printStackTrace();
    			throw ee;
    			
    		}finally{
    			session.flush();
    		}
        	
    	}catch(HibernateException ex) {
    		ex.printStackTrace();
    		throw ex;
    	}
    }
    
    @Override
    public void delete(List<E> e) throws HibernateException {
    	Session session = getCurrentSession();
    	Transaction tx = null;
    	try {
    		tx = session.beginTransaction();    		
    		
    		for(E ee : e) {
    			session.delete(ee);
    		}
    		
    		tx.commit();
    	}catch(HibernateException ex) {
    		if (tx!=null) tx.rollback();
            ex.printStackTrace();
            throw ex;
    	}finally{
    		session.flush();
    	}
    }
    
    @Override
    public void remove(E e) throws HibernateException {
    	try {
    		
    		Session session = getCurrentSession();    	
    		Transaction tx = session.beginTransaction();
    		
    		try{
    			
    			session.saveOrUpdate(e);    			
    			tx.commit();
			}catch(HibernateException ee){
    			
    			if (tx!=null)  tx.rollback();
    			ee.printStackTrace();
    			throw ee;
    			
    		}finally{
    			session.flush();
    		}
    		
    	}catch(HibernateException ex) {
    		ex.printStackTrace();
    		throw ex;
    	}
    }
    
    @Override
    public void remove(List<E> e) throws HibernateException {
    	Session session = getCurrentSession();
    	Transaction tx = null;
    	try {
    		tx = session.beginTransaction();    		
    		
    		for(E ee : e) {
    			session.saveOrUpdate(ee);
    		}
    		
    		tx.commit();
    	}catch(HibernateException ex) {
    		if (tx!=null) tx.rollback();
            ex.printStackTrace();
            throw ex;
    	}finally{
    		
    		session.flush();
    		
    	}
    }

    @Override   
    public List<E> findByCriteria(Criterion criterion) throws HibernateException {
    	try{
    		Session session = getCurrentSession();
    		
    		try{
    			
    			Criteria criteria = session.createCriteria(entityClass);    			
                criteria.add(criterion);
                List<E> lstt = Collections.checkedList(criteria.list(), entityClass);
                       
                return lstt;
    			
    		}catch(HibernateException ex){
        		ex.printStackTrace();
        		throw ex;
        	}finally{
        		session.flush();
        	}
    	}catch(HibernateException e){
    		e.printStackTrace();
    		throw e;
    	}
    	
    }

    /**
     * warning : low performance
     * maxResults if 0 = unlimit
     */
    @Override 
    public List<E> findByAll(int maxResults) throws HibernateException { 
    	try{
    		
    		Session session = getCurrentSession();
    		
    		try{
    			
    			System.out.println("entityClass=" + entityClass);     	    	
    	    	Criteria crit = session.createCriteria(entityClass);
    	    	
    	    	if(maxResults > 0)
    	    		crit.setMaxResults(maxResults);
    	    	
    	    	List<E> lst = crit.list();
    	    	
    	    	if(CommonService.getSize(lst) > 0)    	
    	    		return Collections.checkedList(lst, entityClass);
    	    	else
    	    		return null;
    	    	
    		}catch(HibernateException exx){
    			
    			exx.printStackTrace();
    			throw exx;
    			
    		}finally{
    			session.flush();
    		}
    	}catch(HibernateException e){
    		e.printStackTrace();
    		throw e;
    	}
    }
    
    @Override
    public List<E> findHQLByQuery(String hqlString, HashMap<String, String> parameter) throws HibernateException {    	
    	try {
    		
    		if(CommonService.IsNull(hqlString)) 
    			throw new HibernateException("HQL Name is Empty !!!");    	

    		Session session = getCurrentSession();

    		try{
    			
        		Query query = session.createQuery(hqlString);
        		if(query != null)
        		{
        			if(parameter != null) {
        				Set set = parameter.entrySet(); 
            			Iterator i = set.iterator(); 
            			while(i.hasNext()) {
            				Map.Entry me = (Map.Entry)i.next();         			        			
                			query.setParameter(CommonService.IsNullRString(me.getKey()), CommonService.IsNullRString(me.getValue()));
            			}
        			}    			
        			
        			List list = query.list();
        			if(CommonService.getSize(list) < 0)
        				return null;
        			else
        				return  Collections.checkedList(list, entityClass);				
        		}
        		
    			
    		}catch(HibernateException exx){
    			
    			exx.printStackTrace();
    			throw exx;
    		}finally{
    			session.flush();
    		}
    	}catch(HibernateException e) {    		
    		e.printStackTrace();
    		throw e;
    	}
    	
    	return null;    	
    }
    
    @Override
    public void SaveOrUpdateHQLByQuery(String hqlString, HashMap<String, String> parameter) throws HibernateException {    	
    	try {
    		if(CommonService.IsNull(hqlString)) 
    			throw new HibernateException("HQL Name is Empty !!!");
    		
    		if(parameter == null)
    			throw new HibernateException("HQL Parameter is Empty !!!");
    		
    		Session session = getCurrentSession();
    		try{
    			
    			Query query = session.createQuery(hqlString);
        		if(query != null)
        		{
        			if(parameter != null) {
        				Set set = parameter.entrySet(); 
            			Iterator i = set.iterator(); 
            			while(i.hasNext()) {
            				Map.Entry me = (Map.Entry)i.next();         			        			
                			query.setParameter(CommonService.IsNullRString(me.getKey()), CommonService.IsNullRString(me.getValue()));
            			}	
        			}
        			
        			int irow = query.executeUpdate(); 					
        		}
        		
    			
    		}catch(HibernateException exx){

    			exx.printStackTrace();
    			throw exx;
    		}finally{    			
    			session.flush();
    		}
    		
    	}catch(HibernateException e) {
    		
    		e.printStackTrace();
    		throw e;
    		
    	}
    	return;    	
    }
    
    @Override
    public List<E> findNameQuery(String queryName, HashMap<String, String> parameter) throws HibernateException {
    	try {
			
    		logging.info("queryName = " + queryName);
    		
    		if(CommonService.IsNull(queryName)) 
    			throw new HibernateException("NamedQuery parameter is Empty !!!");
    		
			Session session = getCurrentSession();			
			try{
				
				Query query = session.getNamedQuery(queryName);			
				if(query != null)
	    		{
					if(parameter != null) {
						Set set = parameter.entrySet(); 
		    			Iterator i = set.iterator(); 
		    			while(i.hasNext()) {
		    				Map.Entry me = (Map.Entry)i.next(); 
		    				logging.info("Key=" + CommonService.IsNullRString(me.getKey()) + "/Value=" + CommonService.IsNullRString(me.getValue()));
		        			query.setString(CommonService.IsNullRString(me.getKey()), CommonService.IsNullRString(me.getValue()));
		    			}
					}    			
	    			
					logging.info("query size=" + CommonService.getSize(query.list()));
	    			if(CommonService.getSize(query.list()) > 0 )			
	    				return Collections.checkedList(query.list(), entityClass);
	    			else 
	    				return null;
	    		}
			
				
			}catch(HibernateException exx){
    			exx.printStackTrace();
    			throw exx;
    		}finally{    			
    			session.flush();
    		}
			
		} catch (HibernateException e) {
			
			e.printStackTrace();
			throw e;
		}
    	return null;
    }
    
    @Override
    public void SaveOrUpdateQueryName(String queryName, HashMap<String, String> parameter) throws HibernateException {
    	Transaction tx = null;
    	Session session = getCurrentSession();
    	try {
			
    		if(CommonService.IsNull(queryName)) 
    			throw new HibernateException("NamedQuery Name is Empty !!!");
    		
    		if(parameter == null)
    			throw new HibernateException("NamedQuery Parameter is Empty !!!");
    		
			tx = session.beginTransaction();			
			Query query = session.getNamedQuery(queryName);
			
			if(query != null)
    		{
				if(parameter != null) {
					Set set = parameter.entrySet(); 
	    			Iterator i = set.iterator(); 
	    			while(i.hasNext()) {
	    				Map.Entry me = (Map.Entry)i.next();         			        			
	        			query.setString(CommonService.IsNullRString(me.getKey()), CommonService.IsNullRString(me.getValue()));
	    			}
				}
    			
    			int irow = query.executeUpdate();
    			
    		}
			
			tx.commit();
			
		}catch(HibernateException exx){
			if (tx != null) tx.rollback();
			exx.printStackTrace();
			throw exx;
		}finally{    	
			if(session !=null) session.flush();
		}
    }
    
    public java.util.Date getCurrentDatabaseDate() throws HibernateException, Exception {
    	
    	Session session = getCurrentSession();
		try {
			
			Query q = session.createSQLQuery("SELECT CURRENT_DATE AS COL1 from dual ");
			
			java.util.Date result = (java.util.Date)q.uniqueResult();
			System.out.println("result =" + result);
			
			if(!CommonService.IsNull(result)) {
				return result;
			}
			
		}catch(HibernateException exx){
			exx.printStackTrace();
			throw exx;
		}finally{    	
			if(session !=null) session.flush();
		}
		
		return null;
	}
    
    /**
	 * 
	 * Date =Sep 26, 2013 : 11:29:51 AM 
	 * CreateBy=Chatree Siangklom
	 * TODO
	 * @param name
	 * @return
	 * @throws HibernateException
	 * @throws Exception
	 */
    @Override      
    synchronized
	public BigDecimal NextSequence(String name) throws HibernateException, Exception {    	
    	Session session = getCurrentSession();
    	Transaction tx = null;
		try {
			
			/*
			 CREATE OR REPLACE FUNCTION SYNCHRO.GETFN_SYNC_SEQUENCE( P_NAME IN VARCHAR2)  RETURN VARCHAR2
				IS 
				      PRAGMA AUTONOMOUS_TRANSACTION;
				      CURSOR myCursor IS 
				      SELECT (PRE_FIX||CURRENT_NEXT||SUB_FIX) AS CURRENTNEXT,CURRENT_NEXT,IN_CREMENT  FROM SYNC_SEQUENCE WHERE NAME = P_NAME FOR UPDATE OF   CURRENT_NEXT;
				      V_CURRENT_NEXT NUMBER(20); 
				      V_CURRENT_NEXT_OLD  VARCHAR2(100);            
				    BEGIN
				      FOR idx IN myCursor LOOP
				        V_CURRENT_NEXT  := idx.CURRENT_NEXT + idx.IN_CREMENT;
				        V_CURRENT_NEXT_OLD := idx.CURRENTNEXT; 
				        UPDATE SYNC_SEQUENCE
				        SET CURRENT_NEXT  = V_CURRENT_NEXT 
				       WHERE CURRENT OF myCursor;
				     END LOOP;
				     COMMIT;
				     RETURN  V_CURRENT_NEXT_OLD;
				     EXCEPTION WHEN OTHERS  THEN RETURN '-1';          
				   END;
				/
			 */
			
			tx = session.beginTransaction();
			
			Query q = session.createSQLQuery(" select "+ ClsInstance.INTERFACE_SCHEMA + ClsInstance.POINTER +"GETFN_SYNC_SEQUENCE( :P_NAME ) from dual ");
			q.setParameter("P_NAME", name);
			
			String result = (String)q.uniqueResult();
			//System.out.println("result =" + result);
			
			tx.commit();
			
			if(!CommonService.IsNull(result)) {
				return CommonService.ConvertStringToBigDecimal(result);
			}
			
		}catch(HibernateException exx){
			if (tx != null) tx.rollback();
			exx.printStackTrace();
			throw exx;
		}finally{    	
			if(session !=null) session.flush();
		}
		
		return null;
	}
    
    @Override
    synchronized
    public BigDecimal NextInstanceNo() throws HibernateException, Exception {    	
    	Session session = getCurrentSession();
    	Transaction tx = null;
    	
    	try {
    		
    		/*
			 CREATE OR REPLACE FUNCTION SYNCHRO.GETFN_SYNC_SEQUENCE( P_NAME IN VARCHAR2)  RETURN VARCHAR2
				IS 
				      PRAGMA AUTONOMOUS_TRANSACTION;
				      CURSOR myCursor IS 
				      SELECT (PRE_FIX||CURRENT_NEXT||SUB_FIX) AS CURRENTNEXT,CURRENT_NEXT,IN_CREMENT  FROM SYNC_SEQUENCE WHERE NAME = P_NAME FOR UPDATE OF   CURRENT_NEXT;
				      V_CURRENT_NEXT NUMBER(20); 
				      V_CURRENT_NEXT_OLD  VARCHAR2(100);            
				    BEGIN
				      FOR idx IN myCursor LOOP
				        V_CURRENT_NEXT  := idx.CURRENT_NEXT + idx.IN_CREMENT;
				        V_CURRENT_NEXT_OLD := idx.CURRENTNEXT; 
				        UPDATE SYNC_SEQUENCE
				        SET CURRENT_NEXT  = V_CURRENT_NEXT 
				       WHERE CURRENT OF myCursor;
				     END LOOP;
				     COMMIT;
				     RETURN  V_CURRENT_NEXT_OLD;
				     EXCEPTION WHEN OTHERS  THEN RETURN '-1';          
				   END;
				/
    		 */
    		
    		tx = session.beginTransaction();
    		Query q = session.createSQLQuery(" select "+ ClsInstance.INTERFACE_SCHEMA + ClsInstance.POINTER +"GETFN_SYNC_SEQUENCE( :P_NAME ) from dual ");
    		q.setParameter("P_NAME", "INSTANCE");
    		
    		String result = (String)q.uniqueResult();
    		//System.out.println("result =" + result);
    		
    		tx.commit();
    		
    		if(!CommonService.IsNull(result)) {
    			return CommonService.ConvertStringToBigDecimal(result);
    		}
    		
    	}catch(HibernateException exx){
			if (tx != null) tx.rollback();
			exx.printStackTrace();
			throw exx;
		}finally{    	
			if(session !=null) session.flush();
		}
    	
    	return null;
    }
    
    /** (Javadoc)
     * @see com.tis.database.dao.AbstractDao#StampUsedTime(com.tis.database.entitys.staging.StampObject, java.lang.Object)
     * Date =Oct 18, 2013 : 11:39:41 AM 
     * CreateBy=Chatree Siangklom
     * TODO
     */
    @Override
    public void StampUsedTime(StampObject stampObject, E e) {
    	try{
    		
    		if(stampObject == null)
    			return;
    		
    		if(CommonService.IsNull(stampObject.getInstanceNo()))
    			return;
    		    		
    		String table = "";
    		if(e instanceof SdCustomerOutb1)    		
    			table = "CUSTOMER_OUTB";
    		else if (e instanceof FiInvReceiptOutb)
    			table = "FI_INV_RECEIPT_OUTB";
    		else if (e instanceof FiInvoiceOutb)
    			table = "FI_INVOICE_OUTB";
    		else if (e instanceof FiCreditNoteOutb)
    			table = "FI_CREDIT_NOTE_OUTB";
    		else if (e instanceof FiDepositReturnOutb)
    			table = "FI_DEPOSIT_RETURN_OUTB";
    		else return;
    		
    		Session session = getCurrentSession();
	    	Transaction tx = null;
    		try{
    			tx = session.beginTransaction();
    			
    			StringBuilder sql = new StringBuilder();
    			sql.append("  UPDATE " + ClsInstance.INTERFACE_SCHEMA + ClsInstance.POINTER + table + " SET ");
    			sql.append("  LASTED_STAM_ON = CURRENT_DATE ");
    			
    			if(!CommonService.IsNull(stampObject.getProcessingName()))
    				sql.append("  ,PROCESSING_NAME = ").append(Utilitys.strSql(stampObject.getProcessingName()));

        		if(!CommonService.IsNull(stampObject.getProcessAction()))
        			sql.append(" ,PROCESS_ACTION = ").append(Utilitys.strSql(stampObject.getProcessAction()));
        		
        		if(!CommonService.IsNull(stampObject.getProcessingCount()))
        			sql.append(" ,PROCESSING_COUNT = ").append(stampObject.getProcessingCount());
        		
        		if(!CommonService.IsNull(stampObject.getInstanceNo()))
        			sql.append(" ,REF_INSTANCE_NO = ").append(Utilitys.strSql(stampObject.getInstanceNo()));
        		
        		if(!CommonService.IsNull(stampObject.getProcessLevel()))
        			sql.append(" ,PROCESS_LEVEL = ").append(Utilitys.strSql(stampObject.getProcessLevel()));
        		
        		if(!CommonService.IsNull(stampObject.getProcUseTime()))
        			sql.append(" ,PROC_USE_TIME = ").append(Utilitys.strSql(stampObject.getProcUseTime()));  		
        		
        		if(!CommonService.IsNull(stampObject.getProcStartTime()))
        			sql.append(" ,PROC_START_TIME = CURRENT_DATE ");
        		
        		if(!CommonService.IsNull(stampObject.getProcEndTime()))
        			sql.append(" ,PROC_END_TIME = CURRENT_DATE ");
        		
        		if(!CommonService.IsNull(stampObject.getPmsUseStart()))
        			sql.append(" ,PMS_USE_START = CURRENT_DATE ");
        		
        		if(!CommonService.IsNull(stampObject.getPmsUseEnd()))
        			sql.append(" ,PMS_USE_END = CURRENT_DATE ");
        		
        		if(!CommonService.IsNull(stampObject.getSendUseStart()))
    				sql.append(" ,SEND_USE_START = CURRENT_DATE");
        		
        		if(!CommonService.IsNull(stampObject.getSendUseEnd()))
    				sql.append(" ,SEND_USE_END = CURRENT_DATE");
        		
        		sql.append(" WHERE INSTANCE_NO = ").append(Utilitys.strSql(stampObject.getInstanceNo()));
    			
        		//logging.info("sql=" + sql);
    			Query q = session.createSQLQuery(sql.toString());
    			int updated_result = q.executeUpdate();
    			logging.info("0.Updated Rsult=" + updated_result);
    			tx.commit();
    			session.flush();
    			
    			//**********************************************************
    			session = getCurrentSession();
    			tx = session.beginTransaction();    			
    			sql = new StringBuilder();
    			sql.append(" UPDATE " + ClsInstance.INTERFACE_SCHEMA + ClsInstance.POINTER + table + " SET ");
    			sql.append(" LASTED_STAM_ON = CURRENT_DATE ");
    			sql.append(" ,PROC_CAL = F_CALDATE(PROC_START_TIME, PROC_END_TIME) ");
        		sql.append(" ,PMS_CAL = F_CALDATE(PMS_USE_START, PMS_USE_END) ");
        		sql.append(" ,SEND_CAL = F_CALDATE(SEND_USE_START, SEND_USE_END) ");
    			sql.append(" WHERE INSTANCE_NO = ").append(Utilitys.strSql(stampObject.getInstanceNo()));
    			//logging.info("sql=" + sql);
    			q = session.createSQLQuery(sql.toString());
    			updated_result = q.executeUpdate();
    			logging.info("1.Updated Rsult=" + updated_result);
    			
    			tx.commit();
    		}catch(HibernateException ex ){
    			ex.printStackTrace();
    			
    			if(tx != null) tx.rollback();    			
    			logging.error(ex.getMessage());
    			throw ex;
    		}finally{
    			session.flush();
    		}
    		
    	}catch(Exception exx){
    		exx.printStackTrace();
    		logging.error(exx.getMessage());
    		throw exx;
    	}
    }
    
    protected static String ReplaceStringToTxtDatabase(String value){
		if(value == null) 
			return null;
		else if(value.isEmpty())
			return null;
		else if(value.equals(""))
			return null;
		else
			return value.replace("\'", "''");
	}
    
    /** (Javadoc)
     * @see com.tis.database.dao.AbstractDao#SaveErrorToStaging()
     * Date =Oct 24, 2013 : 5:04:11 PM 
     * CreateBy=Chatree Siangklom
     * TODO
     */
    @Override
    public void SaveErrorToStaging(String instanceNo, STAGING_STATUS Staging_STATUS , String error) {
    	try{    	
    		    	
    		if(CommonService.IsNull(instanceNo))
    			return;
    		
    		String table = "";
    		if(entityClass.equals(SdCustomerOutb1.class))    		
    			table = "CUSTOMER_OUTB";
    		else if (entityClass.equals(FiInvReceiptOutb.class))
    			table = "FI_INV_RECEIPT_OUTB";
    		else if (entityClass.equals(FiInvoiceOutb.class) )
    			table = "FI_INVOICE_OUTB";
    		else if (entityClass.equals(FiCreditNoteOutb.class))
    			table = "FI_CREDIT_NOTE_OUTB";
    		else if (entityClass.equals(FiDepositReturnOutb.class))
    			table = "FI_DEPOSIT_RETURN_OUTB";
    		else return;
    		
    		Session session = getCurrentSession();
	    	Transaction tx = null;
    		try{
    			tx = session.beginTransaction();
    			
    			StringBuilder sql = new StringBuilder();
    			sql.append(" UPDATE " + ClsInstance.INTERFACE_SCHEMA + ClsInstance.POINTER + table + " SET ");
    			sql.append(" LASTED_STAM_ON = CURRENT_DATE ");
    			
    			if(!CommonService.IsNull(error))
    				sql.append(" ,MSG_DESC = ").append(Utilitys.strSql(ReplaceStringToTxtDatabase(CommonService.CutString(error, 1999))));
    			
    			if(Staging_STATUS != null)
    				sql.append(" ,MSG_CODE = ").append(Utilitys.strSql(Staging_STATUS.toString()));
    			
    			sql.append(" WHERE INSTANCE_NO = ").append(Utilitys.strSql(instanceNo));
    			
        		logging.info("sql=" + sql);
        		Query q = session.createSQLQuery(sql.toString());
    			int updated_result = q.executeUpdate();
    			logging.info("Updated Rsult=" + updated_result);    			
    			
    			tx.commit();
    		}catch(HibernateException ex ){
    			ex.printStackTrace();
    			
    			if(tx != null) tx.rollback(); 
    			logging.error(ex.getMessage());
    			throw ex;
    		}finally{
    			session.flush();
    		}
    		
    	}catch(Exception exx){
    		exx.printStackTrace();
    		logging.error(exx.getMessage());
    		throw exx;
    	}
		return; 
    }
    
    /** (Javadoc)
     * @see com.tis.database.dao.AbstractDao#StampUsedTimePms(java.lang.String, java.util.Date, java.util.Date, java.lang.Object)
     * Date =Oct 18, 2013 : 1:29:32 PM 
     * CreateBy=Chatree Siangklom
     * TODO
     */
    @Override
    public void StampUsedTimePms(String instanceNo, Date pmsUseStart, Date pmsUseEnd, E e) throws Exception {
    	
    	StampObject stampObject = new StampObject();
    	stampObject.setInstanceNo(instanceNo);
    	stampObject.setPmsUseStart(pmsUseStart);
    	stampObject.setPmsUseEnd(pmsUseEnd);
    	
    	StampUsedTime(stampObject, e);    	
    }
    /** (Javadoc)
     * @see com.tis.database.dao.AbstractDao#StampUsedTimePROC(java.lang.String, java.util.Date, java.util.Date, java.lang.Object)
     * Date =Oct 18, 2013 : 1:29:36 PM 
     * CreateBy=Chatree Siangklom
     * TODO
     */
    @Override
    public void StampUsedTimePROC(String instanceNo, Date procStartTime, Date procEndTime, E e) throws Exception {
    	
    	StampObject stampObject = new StampObject();
    	stampObject.setInstanceNo(instanceNo);
    	stampObject.setProcStartTime(procStartTime);
    	stampObject.setProcEndTime(procEndTime);
    	
    	StampUsedTime(stampObject, e);    	
    }
    
    /** (Javadoc)
     * @see com.tis.database.dao.AbstractDao#StampUsedTimeSend(java.lang.String, java.util.Date, java.util.Date, java.lang.Object)
     * Date =Oct 18, 2013 : 1:29:38 PM 
     * CreateBy=Chatree Siangklom
     * TODO
     */
    @Override
    public void StampUsedTimeSend(String instanceNo, Date sendUseStart, Date sendUseEnd, E e) throws Exception {
    	
    	StampObject stampObject = new StampObject();
    	stampObject.setInstanceNo(instanceNo);
    	stampObject.setSendUseStart(sendUseStart);
    	stampObject.setSendUseEnd(sendUseEnd);
    	
    	StampUsedTime(stampObject, e);
    	
    }
    
    /** (Javadoc)
     * @see com.tis.database.dao.AbstractDao#findByInstanceNo(java.lang.String, java.lang.Object)
     * Date =Oct 18, 2013 : 1:58:32 PM 
     * CreateBy=Chatree Siangklom
     * TODO
     */
    @Override
    public List<E> findByInstanceNo(String instanceNo, E e) throws HibernateException, Exception {
    	try{
    		
    		if(CommonService.IsNull(instanceNo)) throw new Exception("instanceNo is null !!!");
    		
    		String queryName = "";
    		if(e instanceof SdCustomerOutb1)    		
    			queryName = SdCustomerOutb1.findByInstanceNo;
    		else if (e instanceof FiInvReceiptOutb)
    			queryName = FiInvReceiptOutb.findByInstanceNo;
    		else if (e instanceof FiInvoiceOutb)
    			queryName = FiInvoiceOutb.findByInstanceNo;
    		else if (e instanceof FiCreditNoteOutb)
    			queryName = FiCreditNoteOutb.findByInstanceNo;
    		else if (e instanceof FiDepositReturnOutb)
    			queryName = FiDepositReturnOutb.findByInstanceNo;
    		
    		logging.info("queryName=" + queryName);
    		Session session = getCurrentSession();
    		try{
    			
    			Query q = session.getNamedQuery(queryName);
    			q.setString("instanceNo", instanceNo);    			
    			List<E> lst = q.list();
    			
    			return (CommonService.getSize(lst) > 0) ? (Collections.checkedList(lst, entityClass)) : null;
    			
    		}catch(HibernateException ex){
    			ex.printStackTrace();
    			logging.error(ex.getMessage());
    		}catch(Exception exx){
    			exx.printStackTrace();
    			logging.error(exx.getMessage());
    		}finally{
    			if(session != null)
    				session.flush();
    		}
    	}catch(HibernateException ex){
    		ex.printStackTrace();
    		logging.error(ex.getMessage());
    	}catch(Exception exxx){
    		exxx.printStackTrace();
    		logging.error(exxx.getMessage());
    	}
    	return null;
    }
   
}
